import com.alibaba.excel.EasyExcel;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

public class TestEasyExcelWrite {

    static String jdbc="jdbc:db2://121.37.171.107:55000/testdb";
    static	String user="db2inst1";
    static String pass="1qaz@WSX";
    public static void main(String[] args) {

        String sql1="SELECT * FROM posp.SALES;";

        //实现excel写的操作
        //1 设置写入文件夹地址和excel文件名称
        String filename = "E:\\doc\\write.xlsx";
        // 2 调用easyexcel里面的方法实现写操作
        // write方法两个参数：第一个参数文件路径名称，第二个参数实体类class
        EasyExcel.write(filename,DemoData.class).sheet("销量表").doWrite(DBselect(jdbc,user,pass,sql1));
    }

    //创建方法返回list集合
//    private static List<DemoData> getData() {
//        List<DemoData> list = new ArrayList<>();
//        for (int i = 0; i < 10; i++) {
//            DemoData data = new DemoData();
//            data.setSno(i);
//            data.setSname("lucy"+i);
//            list.add(data);
//        }
//        return list;
//    }

    public static List<DemoData> DBselect(String jdbc,String user,String pass,String sql) {
        Connection conn=null;
        PreparedStatement ps=null;
        ResultSet rs=null;
        List<DemoData> list = new ArrayList<>();
        try{
            Class.forName("com.ibm.db2.jcc.DB2Driver").newInstance();
            conn= DriverManager.getConnection(jdbc,user,pass);
            ps=conn.prepareStatement(sql);
            rs=ps.executeQuery();
            while(rs.next()){
                DemoData data = new DemoData();
                data.setDate(rs.getString(1));
                data.setGoods(rs.getString(2));
                data.setSales(rs.getString(3));
                list.add(data);
                //System.out.println(rs.getString(1)+rs.getString(2)+rs.getString(3));
            }
            conn.close();
            ps.close();
            rs.close();

        }catch(Exception e){
            e.printStackTrace();
        }
        return list;
    }
}